package com.zh.freechat.infrastructure;

import com.zh.mycommon.util.MyDateUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.time.LocalDate;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 维护SQL的脚本
 *
 * @author Zhou
 * @date 14:28 2025/8/12
 */
@Slf4j
@AllArgsConstructor
@Component
public class AutoSQLScript {
    private final JdbcTemplate jdbcTemplate;

    /*
    SELECT
        PARTITION_NAME,          -- 分区名称
        PARTITION_ORDINAL_POSITION,  -- 分区位置顺序
        PARTITION_METHOD,        -- 分区方法（例如 RANGE, LIST 等）
        PARTITION_EXPRESSION,    -- 分区表达式
        PARTITION_DESCRIPTION,   -- 分区描述（对于 RANGE 或 LIST 分区有用）
        TABLE_ROWS,              -- 每个分区中的行数估计值
        DATA_LENGTH,             -- 数据长度
        INDEX_LENGTH             -- 索引长度
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'log_operate';  -- 目标表名
     */

    static String query_partition_names_sql = """
            SELECT  PARTITION_NAME 
            FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? 
            ORDER BY PARTITION_ORDINAL_POSITION ASC 
            """;

    List<String> queryPartitionNames(String tableName) {
        List<String> partitionNames = jdbcTemplate.queryForList(query_partition_names_sql, String.class, tableName);
        log.debug("partitionNames = {}", partitionNames);
        return partitionNames;
    }

    static String query_partition_one_sql = """
            SELECT  PARTITION_NAME 
            FROM INFORMATION_SCHEMA.PARTITIONS 
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? 
            ORDER BY PARTITION_ORDINAL_POSITION DESC limit 1 
            """;

    String queryLastOnePartition(String tableName) {
        try {
            String lastPartition = jdbcTemplate.queryForObject(query_partition_one_sql, String.class, tableName);
            log.debug("partition last = {}", lastPartition);
            return lastPartition;
        } catch (EmptyResultDataAccessException e) {
            log.warn("No partitions found for table: {}", tableName);
            return null;
        }
    }

    static String add_partition_sql_format = """
            ALTER TABLE %s ADD PARTITION (
            %s
            )
            """;

    public void AutoAddPartition() {
        var tableName = "log_operate";

        String lastPartName = queryLastOnePartition(tableName);

        LocalDate baseDate = parsePartitionName(lastPartName);
        LocalDate now = LocalDate.now();

        if (now.getYear() < baseDate.getYear() ||
                (now.getYear() == baseDate.getYear() && now.getMonthValue() < baseDate.getMonthValue())
        ) {
            log.info("Table[{}]分区还充足，无需预添加新的分区", tableName);
            return;
        }

        try {
            var addSql = generateAddPartitionSQL(tableName, lastPartName, 3);
            jdbcTemplate.execute(addSql);
        } catch (DataAccessException ex) {
            log.error("AutoAddPartition 执行SQL时出错 err ={}", ex.getMessage());
        } catch (Exception ex) {
            log.error("AutoAddPartition 执行出错 err ={}", ex.getMessage());
        }
    }


    static String generateAddPartitionSQL(String table, String last, int N) {
        if (N < 0 || N > 12) {
            throw new IllegalArgumentException("N的取值范围需满足 1~12");
        }

        LocalDate baseDate = parsePartitionName(last);

        String[] parts = new String[N];
        for (int i = 0; i < N; i++) {
            LocalDate date1 = baseDate.plusMonths(i + 1);

            var newPartName = "p" + MyDateUtil.formatDate("yyyy_MM", date1);
            var bound = MyDateUtil.formatDate("yyyy-MM-dd", date1.plusMonths(1));

            String str = String.format("\t PARTITION %s VALUES LESS THAN (TO_DAYS('%s'))", newPartName, bound);
            parts[i] = str;
        }


        var partsJoinStr = String.join(",\n", parts);
        return String.format(add_partition_sql_format, table, partsJoinStr);
    }

    public static LocalDate parsePartitionName(String last) {
        // 定义正则表达式：匹配 p + 四位数字（年） + 下划线 + 两位数字（月）
        String regex = "p(\\d{4})_(\\d{2})";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(last);

        if (matcher.matches()) {
            String year = matcher.group(1); // 第一个括号内的内容：年
            String month = matcher.group(2); // 第二个括号内的内容：月

            System.out.println("年: " + year);   // 输出: 2026
            System.out.println("月: " + month);  // 输出: 03

            return LocalDate.of(Integer.parseInt(year), Integer.parseInt(month), 1);
        } else {
            throw new IllegalArgumentException("未匹配到年月信息 " + last);
        }

    }

    public static void main(String[] args) {
        var tableName = "log_operate";
        var last = "p2025_12";

        String sql = generateAddPartitionSQL(tableName, last, 5);

        System.out.println(sql);
    }
}
